package com.colbby.datahouse.db;

import java.beans.PropertyDescriptor;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;

import com.colbby.datahouse.db.impl.DefaultDatabasePropertyMapping;
import com.colbby.datahouse.db.impl.ResultSetIterator;
import com.colbby.datahouse.db.impl.ResultSetList;
import com.colbby.datahouse.mapper.Converter;
import com.colbby.datahouse.mapper.PropertyMapping;
import com.colbby.datahouse.utils.IOUtils;
import com.colbby.datahouse.utils.ReflectUtils;

public class DBAccesser {

	private Connection conn = null;
	
	public DBAccesser(Connection conn) {
		this.conn = conn;
	}
	
	public <T> List<T> query(SqlObject sqlObject, PropertyMapping mapping, Class<T> clazz) {
		ResultSet rs = null;
		List<T> list = new ArrayList<>();
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sqlObject.getSql());
			setParams(ps, sqlObject.getParams());
			rs = ps.executeQuery();
			for(Map<String, Object> map : new ResultSetList(rs)) {
				T t = Converter.convert(map, clazz, mapping);
				list.add(t);
			}
			return list;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			IOUtils.close(rs);
			IOUtils.close(ps);
		}
	}
	
	public <T> T queryOne(SqlObject sqlObject, PropertyMapping mapping, Class<T> clazz) {
		ResultSet rs = null;
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sqlObject.getSql());
			setParams(ps, sqlObject.getParams());
			rs = ps.executeQuery();
			Iterator<Map<String, Object>> it = new ResultSetIterator(rs);
			if(it.hasNext()) {
				return Converter.convert(it.next(), clazz, mapping);
			} else {
				return null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			IOUtils.close(rs);
			IOUtils.close(ps);
		}
	}
	
	private void setParams(PreparedStatement ps, List<Object> params) throws SQLException {
		int i = 1;
		for(Object param : params) {
			ps.setObject(i, param);
			i++;
		}
	}
	
	public <T> List<T> query(SqlObject sqlObject, Class<T> clazz) {
		return query(sqlObject, new DefaultDatabasePropertyMapping(), clazz);
	}
	
	public <T> T queryOne(SqlObject sqlObject, Class<T> clazz) {
		return queryOne(sqlObject, new DefaultDatabasePropertyMapping(), clazz);
	}
	
	public <T> void save(T t, PropertyMapping mapping) {
		if(t == null) return;
		Class<?> clazz = t.getClass();
		SqlObject sqlObject = new SqlObject();
		List<String> proNames = new ArrayList<>();
		List<String> holders = new ArrayList<>();
		sqlObject.append("insert into " + mapping.mapping(clazz.getSimpleName()) + "(");
		for(PropertyDescriptor p : ReflectUtils.pros(clazz)) {
			Method getter = p.getReadMethod();
			if(getter == null) continue;
			proNames.add(mapping.mapping(p.getName()));
			try {
				sqlObject.addParam(getter.invoke(t));
			} catch (Exception e) {
				throw new RuntimeException(e);
			}
			holders.add("?");
		}
		sqlObject.append(StringUtils.join(proNames, ","))
				 .append(")")
				 .append("values(")
				 .append(StringUtils.join(holders, ","))
				 .append(")");
		try {
			sqlObject.executeUpdate(conn);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	
	public <T> void save(T t) {
		save(t, new DefaultDatabasePropertyMapping());
	}
	
	public <T> void update(T updated, T condition, PropertyMapping mapping) {
		if(updated == null || condition == null) return;
		Class<?> clazz = updated.getClass();
		SqlObject sqlObject = new SqlObject();
		List<String> parts = new ArrayList<>();
		sqlObject.append("update " + mapping.mapping(clazz.getSimpleName()) + " set ");
		for(PropertyDescriptor p : ReflectUtils.pros(clazz)) {
			Method getter = p.getReadMethod();
			if(getter == null) continue;
			Object value = ReflectUtils.invoke(getter, updated);
			if(value == null) continue;
			String name = p.getName();
			String dbName = mapping.mapping(name);
			parts.add(dbName + " = ?");
			sqlObject.addParam(value);
		}
		if(parts.isEmpty()) return;
		sqlObject.append(StringUtils.join(parts, ","));
		SqlObject conditionObject = createCondition(condition, mapping);
		if(!conditionObject.isEmpty()) {
			sqlObject.append(" where ");
			sqlObject.merge(conditionObject);
		}
		try {
			sqlObject.executeUpdate(conn);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	
	public <T> void update(T updated, T condition) {
		update(updated, condition, new DefaultDatabasePropertyMapping());
	}
	
	public <T> List<T> select(Class<T> clazz, T condition, PropertyMapping mapping) {
		SqlObject sqlObject = new SqlObject();
		SqlObject conditionObject = createCondition(condition, mapping);
		sqlObject.append("select * from " + mapping.mapping(clazz.getSimpleName()));
		if(!conditionObject.isEmpty()) {
			sqlObject.append(" where ");
			sqlObject.merge(conditionObject);
		}
		return query(sqlObject, mapping, clazz);
	}
	
	public <T> List<T> select(Class<T> clazz, T condition) {
		return select(clazz, condition, new DefaultDatabasePropertyMapping());
	}
	
	public <T> T selectOne(Class<T> clazz, T condition,  PropertyMapping mapping) {
		SqlObject sqlObject = new SqlObject();
		SqlObject conditionObject = createCondition(condition, mapping);
		sqlObject.append("select * from " + mapping.mapping(clazz.getSimpleName()));
		if(!conditionObject.isEmpty()) {
			sqlObject.append(" where ");
			sqlObject.merge(conditionObject);
		}
		return queryOne(sqlObject, mapping, clazz);
	}
	
	public <T> T selectOne(Class<T> clazz, T condition) {
		return selectOne(clazz, condition, new DefaultDatabasePropertyMapping());
	}
	
	private <T> SqlObject createCondition(T t, PropertyMapping mapping) {
		if(t == null) return null;
		Class<?> clazz = t.getClass();
		SqlObject sqlObject = new SqlObject();
		List<String> list = new ArrayList<>();
		for(PropertyDescriptor p : ReflectUtils.pros(clazz)) {
			Method getter = p.getReadMethod();
			if(getter == null) continue;
			Object value = ReflectUtils.invoke(getter, t);
			if(value == null) continue;
			String name = p.getName();
			String dbName = mapping.mapping(name);
			list.add(dbName + " = ?");
			sqlObject.addParam(value);
		}
		return sqlObject.append(StringUtils.join(list, " and "));
	}
}